Imports System.Data.Common
Partial Class Presentation_ClientDetails
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim MySql As String
        Dim CountSql As String
        Dim MyParams(1) As GenParameter

        Page.Title = "See your Client Details"

        'Build the Sql Query Based on the Role the User is in

        If Not User.IsInRole("Top Management") And Not User.IsInRole("Administration") Then

            MySql = "SELECT ClientMaster.ClientName, ClientMaster.Address1, ClientMaster.Address2, ClientMaster.Address3, ClientMaster.City, ClientMaster.Pincode, ContactMaster.ContactName, ContactMaster.Designation, ContactMaster.Telephone, ContactMaster.Mobile, ContactMaster.ContactEmail, ClientMaster.Profile, ClientMaster.Comments" & _
                " FROM (ClientMaster INNER JOIN CompanyAssignments ON ClientMaster.ClientID = CompanyAssignments.ClientID) INNER JOIN ContactMaster ON ClientMaster.ClientID = ContactMaster.ClientID" & _
                " WHERE CompanyAssignments.ExecutiveID = ?ExecutiveID AND ClientMaster.ClientName LIKE CONCAT('%',?ClientPart,'%') AND ContactMaster.DecisionMaker = 1" & _
                " ORDER BY ClientMaster.ClientName"


            CountSql = "SELECT COUNT(*)" & _
                    " FROM (ClientMaster INNER JOIN CompanyAssignments ON ClientMaster.ClientID = CompanyAssignments.ClientID) INNER JOIN ContactMaster ON ClientMaster.ClientID = ContactMaster.ClientID" & _
                    " WHERE CompanyAssignments.ExecutiveID = ?ExecutiveID AND ClientMaster.ClientName LIKE CONCAT('%',?ClientPart,'%') AND ContactMaster.DecisionMaker = 1"

            MyParams(0) = New GenParameter("?ExecutiveID", Request.Cookies("Profile").Item("ExecutiveID"), Data.DbType.UInt32)
            MyParams(1) = New GenParameter("?ClientPart", txtPart.Text, Data.DbType.String)


        Else

            If Not User.IsInRole("Top Management") Then

                MySql = "SELECT ClientMaster.ClientName, ClientMaster.Address1, ClientMaster.Address2, ClientMaster.Address3, ClientMaster.City, ClientMaster.Pincode, ContactMaster.ContactName, ContactMaster.Designation, ContactMaster.Telephone, ContactMaster.Mobile, ContactMaster.ContactEmail, ClientMaster.Profile, ClientMaster.Comments" & _
                " FROM (ClientMaster INNER JOIN (CompanyAssignments INNER JOIN ExecutiveMaster ON CompanyAssignments.ExecutiveID = ExecutiveMaster.ExecutiveID) ON ClientMaster.ClientID = CompanyAssignments.ClientID) INNER JOIN ContactMaster ON ClientMaster.ClientID = ContactMaster.ClientID" & _
                " WHERE  ExecutiveMaster.Location = ?Location AND ClientMaster.ClientName LIKE CONCAT('%',?ClientPart,'%') AND ContactMaster.DecisionMaker = 1" & _
                " ORDER BY ClientMaster.ClientName"


                CountSql = "SELECT COUNT(*)" & _
                " FROM (ClientMaster INNER JOIN (CompanyAssignments INNER JOIN ExecutiveMaster ON CompanyAssignments.ExecutiveID = ExecutiveMaster.ExecutiveID) ON ClientMaster.ClientID = CompanyAssignments.ClientID) INNER JOIN ContactMaster ON ClientMaster.ClientID = ContactMaster.ClientID" & _
                " WHERE  ExecutiveMaster.Location = ?Location AND ClientMaster.ClientName LIKE CONCAT('%',?ClientPart,'%') AND ContactMaster.DecisionMaker = 1" & _
                " ORDER BY ClientMaster.ClientName"

                MyParams(0) = New GenParameter("?Location", Request.Cookies("Profile").Item("Location"), Data.DbType.String)
                MyParams(1) = New GenParameter("?ClientPart", txtPart.Text, Data.DbType.String)

            Else

                MySql = "SELECT ClientMaster.ClientName, ClientMaster.Address1, ClientMaster.Address2, ClientMaster.Address3, ClientMaster.City, ClientMaster.Pincode, ContactMaster.ContactName, ContactMaster.Designation, ContactMaster.Telephone, ContactMaster.Mobile, ContactMaster.ContactEmail, ClientMaster.Profile, ClientMaster.Comments" & _
                " FROM ClientMaster INNER JOIN ContactMaster ON ClientMaster.ClientID = ContactMaster.ClientID" & _
                " WHERE  ClientMaster.ClientName LIKE CONCAT('%',?ClientPart,'%') AND ContactMaster.DecisionMaker = 1" & _
                " ORDER BY ClientMaster.ClientName"


                CountSql = "SELECT COUNT(*)" & _
                " FROM ClientMaster INNER JOIN ContactMaster ON ClientMaster.ClientID = ContactMaster.ClientID" & _
                " WHERE  ClientMaster.ClientName LIKE CONCAT('%',?ClientPart,'%') AND ContactMaster.DecisionMaker = 1" & _
                " ORDER BY ClientMaster.ClientName"

                MyParams(0) = New GenParameter("?Location", Request.Cookies("Profile").Item("Location"), Data.DbType.String)
                MyParams(1) = New GenParameter("?ClientPart", txtPart.Text, Data.DbType.String)


            End If

        End If

        'Assign the Control the values

        ClientDetails.Sql = MySql
        ClientDetails.CountSql = CountSql
        ClientDetails.Params = MyParams
        ClientDetails.ShowButton = True




       
    End Sub
End Class
